/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package wargame.dao;

import connectionmanager.DB;
import wargame.entity.AnalyticProject;

/**
 *
 * @author Clarence
 */
public class AnalyticProjectDAO {

    public static AnalyticProject retrieveAnalyticProject(int analytic_project_id) {
        String sql = "SELECT `analytic_project_id`, `player_id`, `attempt_bid`, `successful_bid`, `project_bid_amount`, `project_max_bid`, `project_submitted`, `project_submitted_on_time`, `project_submitted_late`, `outsourced_project_purchased`, `outsourced_project_submitted`, `outsourced_project_submitted_on_time`, `outsourced_project_submitted_late`, `turn` FROM `analytic_project` WHERE analytic_project_id = '" + analytic_project_id + "'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        AnalyticProject ap = null;
        if (db.next()) {
            ap = populate(db);
        }
        db.close();
        return ap;
    }

    private static AnalyticProject populate(DB db) {
        AnalyticProject ap = new AnalyticProject();
        ap = new AnalyticProject();
        ap.setAnalytic_project_id(db.getInt("analytic_project_id"));
        ap.setPlayer_id(db.getInt("player_id"));
        ap.setAttempt_bid(db.getInt("attempt_bid"));
        ap.setSuccessful_bid(db.getInt("successful_bid"));
        ap.setProject_bid_amount(db.getInt("project_bid_amount"));
        ap.setProject_max_bid(db.getInt("project_max_bid"));
        ap.setProject_submitted(db.getInt("project_submitted"));
        ap.setProject_submitted_on_time(db.getInt("project_submitted_on_time"));
        ap.setProject_submitted_late(db.getInt("project_submitted_late"));
        ap.setOutsourced_project_purchased(db.getInt("outsourced_project_purchased"));
        ap.setOutsourced_project_submitted(db.getInt("outsourced_project_submitted"));
        ap.setOutsourced_project_submitted_on_time(db.getInt("outsourced_project_submitted_on_time"));
        ap.setOutsourced_project_submitted_late(db.getInt("outsourced_project_submitted_late"));
        ap.setTurn(db.getInt("turn"));
        return ap;
    }

    public static int insertAnalyticProject(AnalyticProject ap) {
        String sql = "INSERT INTO analytic_project (`player_id`, `attempt_bid`, `successful_bid`, `project_bid_amount`, `project_max_bid`, `project_submitted`, `project_submitted_on_time`, `project_submitted_late`, `outsourced_project_purchased`, `outsourced_project_submitted`, `outsourced_project_submitted_on_time`, `outsourced_project_submitted_late`, `turn`) VALUES ('" + ap.getPlayer_id() + "', '" + ap.getAttempt_bid() + "', '" + ap.getSuccessful_bid() + "', '" + ap.getProject_bid_amount() + "', '" + ap.getProject_max_bid() + "', '" + ap.getProject_submitted() + "', '" + ap.getProject_submitted_on_time() + "', '" + ap.getProject_submitted_late() + "', '" + ap.getOutsourced_project_purchased() + "', '" + ap.getOutsourced_project_submitted() + "', '" + ap.getOutsourced_project_submitted_on_time() + "', '" + ap.getOutsourced_project_submitted_late() + "', '" + ap.getTurn() + "');";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        int lastInsertedId = db.getLastInsertedID();
        db.close();
        return lastInsertedId;
    }

    public static void updateAnalyticProject(AnalyticProject ap) {
        String sql = "UPDATE analytic_project SET `player_id` = '" + ap.getPlayer_id() + "', `attempt_bid` = '" + ap.getAttempt_bid() + "', `successful_bid` = '" + ap.getSuccessful_bid() + "', `project_bid_amount` = '" + ap.getProject_bid_amount() + "', `project_max_bid` = '" + ap.getProject_max_bid() + "', `project_submitted` = '" + ap.getProject_submitted() + "', `project_submitted_on_time` = '" + ap.getProject_submitted_on_time() + "', `project_submitted_late` = '" + ap.getProject_submitted_late() + "', `outsourced_project_purchased` = '" + ap.getOutsourced_project_purchased() + "', `outsourced_project_submitted` = '" + ap.getOutsourced_project_submitted() + "', `outsourced_project_submitted_on_time` = '" + ap.getOutsourced_project_submitted_on_time() + "', `outsourced_project_submitted_late` = '" + ap.getOutsourced_project_submitted_late() + "', `turn` = '" + ap.getTurn() + "' WHERE analytic_project_id = '" + ap.getAnalytic_project_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void deleteAnalyticProject(int analytic_project_id) {
        String sql = "DELETE FROM analytic_project WHERE analytic_project_id = '" + analytic_project_id + "'";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static int getProjectBidCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS project_bids FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND attempt_bid = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int project_count = 0;
        if (db.next()) {
            project_count = db.getInt("project_bids");
        }
        return project_count;
    }

    public static int getSuccessfulProjectBidCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS project_bids FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND successful_bid = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int project_count = 0;
        if (db.next()) {
            project_count = db.getInt("project_bids");
        }
        return project_count;
    }

    public static int getOSProjectPurchased(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS outsourced_project_purchased_count FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND outsourced_project_purchased = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int outsourced_project_purchased_count = 0;
        if (db.next()) {
            outsourced_project_purchased_count = db.getInt("outsourced_project_purchased_count");
        }
        return outsourced_project_purchased_count;
    }

    // PROJECT
    public static int getProjectSubmittedCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS project_submitted_count FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND project_submitted = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int project_submitted_count = 0;
        if (db.next()) {
            project_submitted_count = db.getInt("project_submitted_count");
        }
        return project_submitted_count;
    }

    public static int getProjectSubmittedLateCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS project_submitted_late_count FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND project_submitted_late = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int project_submitted_late_count = 0;
        if (db.next()) {
            project_submitted_late_count = db.getInt("project_submitted_late_count");
        }
        return project_submitted_late_count;
    }

    public static int getProjectSubmittedOnTimeCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS project_submitted_on_time_count FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND project_submitted_on_time = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int project_submitted_on_time_count = 0;
        if (db.next()) {
            project_submitted_on_time_count = db.getInt("project_submitted_on_time_count");
        }
        return project_submitted_on_time_count;
    }

    // OS PROJECT
    public static int getOSProjectSubmittedCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS os_project_submitted_count FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND outsourced_project_submitted = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int os_project_submitted_count = 0;
        if (db.next()) {
            os_project_submitted_count = db.getInt("os_project_submitted_count");
        }
        return os_project_submitted_count;
    }

    public static int getOSProjectSubmittedLateCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS os_project_submitted_late_count FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND outsourced_project_submitted_late = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int os_project_submitted_late_count = 0;
        if (db.next()) {
            os_project_submitted_late_count = db.getInt("os_project_submitted_late_count");
        }
        return os_project_submitted_late_count;
    }

    public static int getOSProjectSubmittedOnTimeCount(int player_id, int turn) {
        String sql = "SELECT COUNT(*) AS os_project_submitted_on_time_count FROM `analytic_project` WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND outsourced_project_submitted_on_time = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int os_project_submitted_on_time_count = 0;
        if (db.next()) {
            os_project_submitted_on_time_count = db.getInt("os_project_submitted_on_time_count");
        }
        return os_project_submitted_on_time_count;
    }

    // AMOUNT
    public static int getProjectBidTotal(int player_id, int turn) {
        String sql = "SELECT SUM(project_bid_amount) AS project_bid_total FROM analytic_project WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND attempt_bid = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int project_bid_amount = 0;
        if (db.next()) {
            project_bid_amount = db.getInt("project_bid_total");
        }
        return project_bid_amount;
    }

    public static int getProjectMaxTotal(int player_id, int turn) {
        String sql = "SELECT SUM(project_max_bid) AS project_max_total FROM analytic_project WHERE `player_id` ='" + player_id + "' AND `turn` = '" + turn + "' AND attempt_bid = '1'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int project_max_total = 0;
        if (db.next()) {
            project_max_total = db.getInt("project_max_total");
        }
        return project_max_total;
    }
}
